﻿namespace MxWeiXinPF.DAL
{
    using MxWeiXinPF.Common;
    using MxWeiXinPF.DBUtility;
    using System;
    using System.Data;
    using System.Data.SqlClient;
    using System.Runtime.InteropServices;
    using System.Text;

    public class user_rate
    {
        private string databaseprefix;

        public user_rate(string _databaseprefix)
        {
            this.databaseprefix = _databaseprefix;
        }

        public int Add(int wid, string openid, string truename, string bank, string bankNO, string phone, string address)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("insert into " + this.databaseprefix + "user_bank(");
            builder.Append("wid,openid,truename,bank,bankNO,phone,address)");
            builder.Append(" values (");
            builder.Append("@wid,@openid,@truename,@bank,@bankNO,@phone,@address)");
            builder.Append(";select @@IDENTITY");
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@wid", SqlDbType.Int), new SqlParameter("@openid", SqlDbType.VarChar, 100), new SqlParameter("@truename", SqlDbType.VarChar, 50), new SqlParameter("@bank", SqlDbType.VarChar, 100), new SqlParameter("@bankNO", SqlDbType.VarChar, 100), new SqlParameter("@phone", SqlDbType.VarChar, 50), new SqlParameter("@address", SqlDbType.VarChar, 200) };
            cmdParms[0].Value = wid;
            cmdParms[1].Value = openid;
            cmdParms[2].Value = truename;
            cmdParms[3].Value = bank;
            cmdParms[4].Value = bankNO;
            cmdParms[5].Value = phone;
            cmdParms[6].Value = address;
            object single = DbHelperSQL.GetSingle(builder.ToString(), cmdParms);
            if (single == null)
            {
                return 0;
            }
            return Convert.ToInt32(single);
        }

        public int AddPoints(int wid, int shopid,string openid, int uid, string status, string ratetype, string remarks)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("insert into " + this.databaseprefix + "user_rate(");
            builder.Append("wid,openid,uid,status,ratetype,remarks,confirmcode,shopid)");
            builder.Append(" values (");
            builder.Append("@wid,@openid,@uid,@status,@ratetype,@remarks,@confirmcode,@shopid)");
            builder.Append(";select @@IDENTITY");
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@wid", SqlDbType.Int), new SqlParameter("@openid", SqlDbType.VarChar, 100), new SqlParameter("@uid", SqlDbType.Int), new SqlParameter("@status", SqlDbType.VarChar, 100), new SqlParameter("@ratetype", SqlDbType.VarChar, 100), new SqlParameter("@remarks", SqlDbType.VarChar, 0x7d0), new SqlParameter("@confirmcode", SqlDbType.VarChar, 20),new SqlParameter("@shopid", SqlDbType.Int) };
            cmdParms[0].Value = wid;
            cmdParms[1].Value = openid;
            cmdParms[2].Value = uid;
            cmdParms[3].Value = status;
            cmdParms[4].Value = ratetype;
            cmdParms[5].Value = remarks;
            cmdParms[6].Value = uid.ToString().PadLeft(6, '0') + Utils.Number(2, true);
            cmdParms[7].Value = shopid;
            object single = DbHelperSQL.GetSingle(builder.ToString(), cmdParms);
            if (single == null)
            {
                return 0;
            }
            return Convert.ToInt32(single);
        }

        public bool AdminDelete(int id)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("update " + this.databaseprefix + "user_rate ");
            builder.Append(" set shopid=null,amount=0");
            builder.Append(" where id=@id");
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@id", SqlDbType.Int, 4) };
            cmdParms[0].Value = id;
            return (DbHelperSQL.ExecuteSql(builder.ToString(), cmdParms) > 0);
        }

        public DataSet AdminGetListByConfirmCode(string confirmcode)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select ");
            builder.Append(" top 1 *  ");
            builder.Append(" FROM " + this.databaseprefix + "user_rate ");
            builder.Append(" where confirmcode='" + confirmcode + "' ");
            return DbHelperSQL.Query(builder.ToString());
        }

        public DataSet AdminGetListByID(int id)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select ");
            builder.Append(" top 1 *  ");
            builder.Append(" FROM " + this.databaseprefix + "user_rate ");
            builder.Append(" where id='" + id + "' ");
            return DbHelperSQL.Query(builder.ToString());
        }

        public DataSet AdminGetListScore(int wid, int pageSize, int pageIndex, string strWhere, string filedOrder, out int recordCount)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append(" select a.*,b.nick_name from dt_user_rate a left join dt_users b on a.uid=b.id ");
            builder.Append(" where a.ratetype='3' and a.wid=" + wid);
            if (strWhere.Trim() != "")
            {
                builder.Append("  and  " + strWhere);
            }
            recordCount = Convert.ToInt32(DbHelperSQL.GetSingle(PagingHelper.CreateCountingSql(builder.ToString())));
            return DbHelperSQL.Query(PagingHelper.CreatePagingSql(recordCount, pageSize, pageIndex, builder.ToString(), filedOrder));
        }

        public DataSet AdminGetListScoreEx(int wid, int shopid,int pageSize, int pageIndex, string strWhere, string filedOrder, out int recordCount)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append(" select a.*,b.nick_name from dt_user_rate a left join dt_users b on a.uid=b.id ");
            builder.Append(" where a.ratetype='3' and a.wid=" + wid);
            if (strWhere.Trim() != "")
            {
                builder.Append("  and  " + strWhere);
            }
            recordCount = Convert.ToInt32(DbHelperSQL.GetSingle(PagingHelper.CreateCountingSql(builder.ToString())));
            return DbHelperSQL.Query(PagingHelper.CreatePagingSql(recordCount, pageSize, pageIndex, builder.ToString(), filedOrder));
        }

        public bool AuditExists(int id)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select count(1) from " + this.databaseprefix + "user_rate");
            builder.Append(" where id=@id and status='1'");
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@id", SqlDbType.Int) };
            cmdParms[0].Value = id;
            return DbHelperSQL.Exists(builder.ToString(), cmdParms);
        }

        public bool AuditPoints(string status, string confirmcode, int id)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("update " + this.databaseprefix + "user_rate set ");
            builder.Append(" status=@status ");
            builder.Append(" where confirmcode=@confirmcode and id=@id");
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@status", SqlDbType.VarChar, 2), new SqlParameter("@confirmcode", SqlDbType.VarChar, 10), new SqlParameter("@id", SqlDbType.Int) };
            cmdParms[0].Value = status;
            cmdParms[1].Value = confirmcode;
            cmdParms[2].Value = id;
            return (DbHelperSQL.ExecuteSql(builder.ToString(), cmdParms) > 0);
        }

        public bool Delete(int id)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("delete from " + this.databaseprefix + "user_rate ");
            builder.Append(" where id=@id");
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@id", SqlDbType.Int, 4) };
            cmdParms[0].Value = id;
            return (DbHelperSQL.ExecuteSql(builder.ToString(), cmdParms) > 0);
        }

        public DataSet GetBank(string openid)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select ");
            builder.Append(" id,wid,openid,trueName,bank,bankNO,phone,address  ");
            builder.Append(" FROM " + this.databaseprefix + "user_Bank ");
            builder.Append(" where openid='" + openid + "' ");
            return DbHelperSQL.Query(builder.ToString());
        }

        public DataSet GetBankByID(int id)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select ");
            builder.Append(" id,wid,openid,trueName,bank,bankNO,phone,address ");
            builder.Append(" FROM " + this.databaseprefix + "user_Bank ");
            builder.Append(" where openid=" + id);
            return DbHelperSQL.Query(builder.ToString());
        }

        public string GetCode(int id)
        {
            StringBuilder builder = new StringBuilder();
            if (id == 0)
            {
                builder.Append("select top 1 confirmcode from  " + this.databaseprefix + "user_rate ");
                builder.Append("  where status='0'");
            }
            else
            {
                builder.Append("select confirmcode from  " + this.databaseprefix + "user_rate ");
                builder.Append("  where id=@id");
            }
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@id", SqlDbType.Int, 4) };
            cmdParms[0].Value = id;
            object single = DbHelperSQL.GetSingle(builder.ToString(), cmdParms);
            if (single == null)
            {
                return "";
            }
            return single.ToString();
        }

        public DataSet GetDateSet(int Top, int wid, string startdate, string enddate, string keyword)
        {
            SqlParameter[] parameters = new SqlParameter[] { new SqlParameter("@Top", SqlDbType.Int, 4), new SqlParameter("@wid", SqlDbType.Int, 4), new SqlParameter("@startdate", SqlDbType.DateTime), new SqlParameter("@enddate", SqlDbType.DateTime), new SqlParameter("@keyword", SqlDbType.VarChar, 50) };
            parameters[0].Value = Top;
            parameters[1].Value = wid;
            if (!startdate.Equals(""))
            {
                parameters[2].Value = DateTime.Parse(startdate);
            }
            else
            {
                parameters[2].Value = DBNull.Value;
            }
            if (!enddate.Equals(""))
            {
                parameters[3].Value = DateTime.Parse(enddate);
            }
            else
            {
                parameters[3].Value = DBNull.Value;
            }
            if (!keyword.Equals(""))
            {
                parameters[4].Value = keyword;
            }
            else
            {
                parameters[4].Value = DBNull.Value;
            }
            return DbHelperSQL.RunProcedure("sp_UserRateSelect", parameters, "user_rate");
        }

        public DataSet GetDateSet(int wid, int shopid, int pageSize, int pageIndex, string startdate, string enddate, string keyword, out int recordCount)
        {
            SqlParameter[] parameters = new SqlParameter[] { new SqlParameter("@wid", SqlDbType.Int, 4), new SqlParameter("@startdate", SqlDbType.DateTime), new SqlParameter("@enddate", SqlDbType.DateTime), new SqlParameter("@keyword", SqlDbType.VarChar, 50), new SqlParameter("@shopid", SqlDbType.Int, 4) };
            parameters[0].Value = wid;
            if (!startdate.Equals(""))
            {
                parameters[1].Value = DateTime.Parse(startdate);
            }
            else
            {
                parameters[1].Value = DBNull.Value;
            }
            if (!enddate.Equals(""))
            {
                parameters[2].Value = DateTime.Parse(enddate);
            }
            else
            {
                parameters[2].Value = DBNull.Value;
            }
            if (!keyword.Equals(""))
            {
                parameters[3].Value = keyword;
            }
            else
            {
                parameters[3].Value = '%';
            }
            if (shopid != 0x27)
            {
                parameters[4].Value = shopid;
            }
            else
            {
                parameters[4].Value = 0;
            }
            DataSet set = DbHelperSQL.RunProcedure("sp_UserRateSelect", parameters, "user_rate");
            recordCount = set.Tables.Count;
            return set;
        }

        public DataSet GetDateSetByOpenID(string openid)
        {
            SqlParameter[] parameters = new SqlParameter[] { new SqlParameter("@openid", SqlDbType.VarChar, 100) };
            parameters[0].Value = openid;
            return DbHelperSQL.RunProcedure("sp_RateSelectByOpenID", parameters, "user_rate");
        }

        public DataSet GetDateSetByWidOpenID(string openid)
        {
            SqlParameter[] parameters = new SqlParameter[] { new SqlParameter("@openid", SqlDbType.VarChar, 100) };
            parameters[0].Value = openid;
            return DbHelperSQL.RunProcedure("sp_UserRateSelectByWidOpenID", parameters, "user_rate");
        }

        public DataSet GetDateSetForExcel(int wid, int shopid, string startdate, string enddate, string keyword, out int recordCount)
        {
            SqlParameter[] parameters = new SqlParameter[] { new SqlParameter("@wid", SqlDbType.Int, 4), new SqlParameter("@startdate", SqlDbType.DateTime), new SqlParameter("@enddate", SqlDbType.DateTime), new SqlParameter("@keyword", SqlDbType.VarChar, 50), new SqlParameter("@shopid", SqlDbType.Int, 4) };
            parameters[0].Value = wid;
            if (!startdate.Equals(""))
            {
                parameters[1].Value = DateTime.Parse(startdate);
            }
            else
            {
                parameters[1].Value = DBNull.Value;
            }
            if (!enddate.Equals(""))
            {
                parameters[2].Value = DateTime.Parse(enddate);
            }
            else
            {
                parameters[2].Value = DBNull.Value;
            }
            if (!keyword.Equals(""))
            {
                parameters[3].Value = keyword;
            }
            else
            {
                parameters[3].Value = '%';
            }
            parameters[4].Value = shopid;
            DataSet set = DbHelperSQL.RunProcedure("sp_UserRateSelect", parameters, "user_rate");
            recordCount = set.Tables.Count;
            return set;
        }

        public DataSet GetList(int Top, string strWhere, string filedOrder)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select ");
            if (Top > 0)
            {
                builder.Append(" top " + Top.ToString());
            }
            builder.Append(" id,order_no,trade_no,user_id,user_name,payment_id,payment_fee,payment_status,payment_time,express_id,express_no,express_fee,express_status,express_time,accept_name,post_code,telphone,mobile,area,address,message,remark,payable_amount,real_amount,order_amount,point,status,add_time,confirm_time,complete_time,wid,openid ");
            builder.Append(" FROM " + this.databaseprefix + "orders ");
            if (strWhere.Trim() != "")
            {
                builder.Append(" where " + strWhere);
            }
            builder.Append(" order by " + filedOrder);
            return DbHelperSQL.Query(builder.ToString());
        }

        public DataSet GetList(int wid, int pageSize, int pageIndex, string strWhere, string filedOrder, out int recordCount)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append(string.Concat(new object[] { " select (select top 1  title from dt_payment p where o.payment_id =p.pTypeId and p.wid=", wid, ") as paymentName , (select  top 1 title from dt_express e where e.id=o.express_id and e.wid=", wid, ") as expressName,'' as statusName, o.* from dt_orders o " }));
            builder.Append(" where o.wid=" + wid);
            if (strWhere.Trim() != "")
            {
                builder.Append("  and  " + strWhere);
            }
            recordCount = Convert.ToInt32(DbHelperSQL.GetSingle(PagingHelper.CreateCountingSql(builder.ToString())));
            return DbHelperSQL.Query(PagingHelper.CreatePagingSql(recordCount, pageSize, pageIndex, builder.ToString(), filedOrder));
        }

        public DataSet GetListScore(int wid, int pageSize, int pageIndex, string strWhere, string filedOrder, out int recordCount)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append(" select a.*,b.real_name shopname from dt_user_rate a left join dt_manager b on a.shopid=b.id ");
            builder.Append(" where a.ratetype='3' and a.wid=" + wid);
            if (strWhere.Trim() != "")
            {
                builder.Append("  and  " + strWhere);
            }
            recordCount = Convert.ToInt32(DbHelperSQL.GetSingle(PagingHelper.CreateCountingSql(builder.ToString())));
            return DbHelperSQL.Query(PagingHelper.CreatePagingSql(recordCount, pageSize, pageIndex, builder.ToString(), filedOrder));
        }

        public bool Update(int wid, string openid, string truename, string bank, string bankNO, string phone, string address, int id)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("update " + this.databaseprefix + "user_bank set ");
            builder.Append("wid=@wid,");
            builder.Append("openid=@openid,");
            builder.Append("truename=@truename,");
            builder.Append("bank=@bank,");
            builder.Append("bankNO=@bankNO,");
            builder.Append("phone=@phone,");
            builder.Append("address=@address ");
            builder.Append(" where id=@id");
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@wid", SqlDbType.Int), new SqlParameter("@openid", SqlDbType.VarChar, 100), new SqlParameter("@truename", SqlDbType.VarChar, 50), new SqlParameter("@bank", SqlDbType.VarChar, 100), new SqlParameter("@bankNO", SqlDbType.VarChar, 100), new SqlParameter("@phone", SqlDbType.VarChar, 50), new SqlParameter("@address", SqlDbType.VarChar, 200), new SqlParameter("@id", SqlDbType.Int) };
            cmdParms[0].Value = wid;
            cmdParms[1].Value = openid;
            cmdParms[2].Value = truename;
            cmdParms[3].Value = bank;
            cmdParms[4].Value = bankNO;
            cmdParms[5].Value = phone;
            cmdParms[6].Value = address;
            cmdParms[7].Value = id;
            return (DbHelperSQL.ExecuteSql(builder.ToString(), cmdParms) > 0);
        }

        public bool UpdatePoints(int shopid, decimal amount, string remarks, string confirmcode, int id)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("update " + this.databaseprefix + "user_rate set ");
            builder.Append("shopid=@shopid,");
            builder.Append("amount=@amount,");
            builder.Append("remarks=@remarks");
            builder.Append(" where confirmcode=@confirmcode and id=@id");
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@shopid", SqlDbType.Int), new SqlParameter("@amount", SqlDbType.Decimal), new SqlParameter("@remarks", SqlDbType.VarChar, 0x3e8), new SqlParameter("@confirmcode", SqlDbType.VarChar, 10), new SqlParameter("@id", SqlDbType.Int) };
            cmdParms[0].Value = shopid;
            cmdParms[1].Value = amount;
            cmdParms[2].Value = remarks;
            cmdParms[3].Value = confirmcode;
            cmdParms[4].Value = id;
            return (DbHelperSQL.ExecuteSql(builder.ToString(), cmdParms) > 0);
        }
    }
}

